﻿using ConsoleApp2.Migrations;
using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using System.Collections.Generic;
using System.Data.Common;
using System.Drawing;
using System.Linq;

namespace ConsoleApp2
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello, World!");

            //AddArticle();
            //TestAddArticle();
            //TestAddComment();
            //TestAddCommentSelect();
            //TestAddArticleSelect();
            //TestAddCommentForeignKey();

            //AddLeave();
            //AddOrgUnit();
            //TestAddOrgUnit();

            //AddOrderDelivery();
            //AddTeacherStudent();
            //TestAddTeacherStudent();

            //TestIQueryable();

            //AddBooks();
            //TestSqlInterpolated();
            //TestFromSqlInterpolated();

            //TestDbConnection();
            //TestDapper();

            //TestEntityEntry();
            //TestEntityEntryAsNoTracking();

            //TestUpdateNoSearch();

            TestSqlBulk();
        }

        static void TestSqlBulk()
        {
            using (var ctx = new AppDbContext())
            {
                var items = ctx.Articles.ToArray();
                foreach (var e in items)
                {
                    e.Content += " More... ";
                }

                Article a1 = new Article { Title = "SqlBulk 1", Content = "More." };
                Article a2 = new Article { Title = "SqlBulk 2", Content = "More." };
                ctx.Articles.AddRange(new[] { a1, a2 });

                ctx.SaveChanges();
            }
        }

        static void TestUpdateNoSearch()
        {
            using(var ctx = new AppDbContext())
            {
                Book b1 = new Book { Id = 8 }; //跟踪通过Id定位
                b1.Title = "yzk good job";

                var entry1 = ctx.Entry(b1);
                entry1.Property("Title").IsModified = true;

                Console.WriteLine(entry1.DebugView.LongView);
                ctx.SaveChanges();
            }
        }

        static void TestEntityEntryAsNoTracking()
        {
            using (var ctx = new AppDbContext())
            {
                var items = ctx.Articles.AsNoTracking()
                    .Take(3).ToArray();
                foreach(var e in items)
                {
                    Console.WriteLine(e.Title);
                }

                var a1 = items[0];
                a1.Content += " More...";

                Console.WriteLine(ctx.Entry(a1).State); //Detached

                ctx.SaveChanges(); //更新不生效
            }
        }

        static void TestEntityEntry()
        {
            using (var ctx = new AppDbContext())
            {
                var items = ctx.Articles.Take(3).ToArray();
                var a1 = items[0];
                var a2 = items[1];
                var a3 = items[2];
                var a4 = new Article { Title = "EntityEntry a4" };
                var a5 = new Article { Title = "EntityEntry a5" };

                a1.Content += " More...";
                ctx.Remove(a2);
                ctx.Articles.Add(a4);

                EntityEntry e1 = ctx.Entry(a1);
                EntityEntry e2 = ctx.Entry(a2);
                EntityEntry e3 = ctx.Entry(a3);
                EntityEntry e4 = ctx.Entry(a4);
                EntityEntry e5 = ctx.Entry(a5);

                Console.WriteLine(e1.State); // Modified
                Console.WriteLine(e2.State); // Deleted
                Console.WriteLine(e3.State); // Unchanged
                Console.WriteLine(e4.State); // Added
                Console.WriteLine(e5.State); // Detached

                Console.WriteLine(e1.DebugView.LongView);
                //Article { Id: 1} Modified
                //    Id: 1 PK
                //    Content: '据报道... More...' Modified Originally '据报道...'
                //    Title: '杨中科被评为亚洲最酷程序员'
                //  Comments: []
            }
        }

        static void TestDapper()
        {
            using (var ctx = new AppDbContext())
            {
                var items = ctx.Database.GetDbConnection()
                    .Query<GroupArticleByPrice>("select Price, Count(*) PCount from T_Books group by Price");
                foreach (var item in items)
                    Console.WriteLine(item.Price + ":" + item.PCount);
            }
        }

        static void TestDbConnection()
        {
            using (var ctx = new AppDbContext())
            {
                DbConnection conn = ctx.Database.GetDbConnection(); // 拿到Context对应的底层Connection
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }
                using (var cmd = conn.CreateCommand())
                {
                    int price = 100;
                    cmd.CommandText = @"
            select Price, Count(*) from T_Books 
            where Price > @price
            group by Price having Count(*) > 0;";
                    var p1 = cmd.CreateParameter();
        p1.ParameterName = "@price";
                    p1.Value = price;
                    cmd.Parameters.Add(p1);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            double p = reader.GetDouble(0);
                            int count = reader.GetInt32(1);
                            Console.WriteLine($"{p} :{count}");
                        }
                    }
                }
            }
        }

        static void TestFromSqlInterpolated()
        {
            int year = 2010;
            using (var ctx = new AppDbContext())
            {
                IQueryable<Book> books = ctx.Books
                    .FromSqlInterpolated(@$"
select * from T_Books 
where DatePart(year,PubTime) > {year}
order by newid()"); // 随机排序

                foreach(var b in books)
                {
                    Console.WriteLine(b);
                }
            }
            using (var ctx = new AppDbContext())
            {
                IQueryable<Book> queryable = ctx.Books.FromSqlInterpolated(@$"
select * from T_Books 
where DatePart(year,PubTime)>{year}");
                foreach (Book b in queryable.OrderBy(b => Guid.NewGuid()).Skip(3).Take(6))
                {
                    Console.WriteLine(b.Title);
                }
            }
        }

        static void TestSqlInterpolated()
        {
            string aName = "虚假故事会";
            int price = 100;
            using (var ctx = new AppDbContext())
            {
                ctx.Database.ExecuteSqlInterpolated($@"
insert into T_Books(Title, PubTime, Price, AuthorName) 
select Title, PubTime, Price, {aName} 
from T_Books where Price > 
                {price}
");
                //insert into T_Books(Title, PubTime, Price, AuthorName)
                //  select Title, PubTime, Price, @p0
                //  from T_Books where Price > @p1
            }
        }


        static void AddBooks()
        {
            using (var ctx = new AppDbContext())
            {
                Book book = new Book()
                {
                    Title = "架构整洁之道",
                    AuthorName = "罗伯特.马丁",
                    Price = 128,
                    PubTime = new DateTime(2018, 9, 1)
                };
                ctx.Books.Add(book);
                Book book1 = new Book()
                {
                    Title = "敏捷软件开发（珍藏版）",
                    AuthorName = "罗伯特.马丁",
                    Price = 108,
                    PubTime = new DateTime(2021, 1, 1)
                };
                ctx.Books.Add(book1);
                Book book2 = new Book()
                {
                    Title = "实现领域驱动设计",
                    AuthorName = "沃恩.佛农",
                    Price = 68,
                    PubTime = new DateTime(2014, 3, 1)
                };
                ctx.Books.Add(book2);
                ctx.SaveChanges();
            }
        }

        static void TestIQueryable()
        {
            using (var ctx = new AppDbContext())
            {
                IQueryable<Article> articles = ctx.Articles
                    .Where(a => a.Title.Contains("微软"));
                foreach(var a in articles)
                {
                    Console.WriteLine(a.Title);
                }
            }
        }

        static void TestAddTeacherStudent()
        {
            using (var ctx = new AppDbContext())
            {
                var teachers = ctx.Teachers.Include(t => t.Students);
                foreach(var t in teachers)
                {
                    Console.WriteLine(t.Name);
                    foreach(var s in t.Students)
                    {
                        Console.WriteLine("  " + s.Name);
                    }
                }
            }
        }

        static void AddTeacherStudent()
        {
            using (var ctx = new AppDbContext())
            {
                Student s1 = new Student{ Name = "张三" };
                Student s2 = new Student{ Name = "李逵" };
                Student s3 = new Student{ Name = "王一博" };
                ctx.Students.AddRange(new[] { s1, s2, s3 });

                Teacher t1 = new Teacher { Name = "tom" };
                Teacher t2 = new Teacher { Name = "jerry" };
                Teacher t3 = new Teacher { Name = "jack" };
                Teacher t4 = new Teacher { Name = "rose" };
                ctx.Teachers.AddRange(new[] { t1, t2, t3, t4 });

                t1.Students.AddRange(new[] { s1, s2 });
                t2.Students.AddRange(new[] { s2, s3 });
                t3.Students.AddRange(new[] { s1, s3 });
                t4.Students.AddRange(new[] { s1, s2, s3 });

                ctx.SaveChanges();
            }
        }

        static void AddOrderDelivery()
        {
            using (var ctx = new AppDbContext())
            {
                Order o = new Order { Name = "老九门", Address = "长沙" };
                Delivery d = new Delivery { CompanyName = "科技", Number="1", Order = o };

                ctx.Deliveries.Add(d);
                ctx.SaveChanges();
            }
        }

        static void TestAddOrgUnit()
        {
            using (var ctx = new AppDbContext())
            {
                OrgUnit u = ctx.OrgUnits.Where(u => u.Parent == null)
                    .FirstOrDefault();
                if (u != null)
                    PrintChildren(ctx, 0, u);
            }
        }

        private static void PrintChildren(AppDbContext ctx, int indentLevel, OrgUnit parent)
        {
            var children = ctx.OrgUnits.Where(u => u.Parent == parent);
            foreach(var child in children)
            {
                Console.WriteLine(new String(' ', 4 * indentLevel) + child.Name);
                PrintChildren(ctx, indentLevel + 1, child);
            }
        }

        static void AddOrgUnit()
        {
            using (var ctx = new AppDbContext())
            {
                OrgUnit u = new OrgUnit { Name = "老九门" };
                ctx.OrgUnits.Add(u);

                OrgUnit u1 = new OrgUnit { Name = "老九门-吴家", Parent=u };
                OrgUnit u2 = new OrgUnit { Name = "老九门-霍家", Parent = u };
                OrgUnit u3 = new OrgUnit { Name = "老九门-张家", Parent = u };
                ctx.OrgUnits.AddRange(new[] {u1, u2, u3});

                OrgUnit u11 = new OrgUnit { Name = "老九门-吴家-吴老狗", Parent = u1 };
                OrgUnit u21 = new OrgUnit { Name = "老九门-霍家-霍仙姑", Parent = u2 };
                OrgUnit u31 = new OrgUnit { Name = "老九门-张家-张启山", Parent = u3 };
                ctx.OrgUnits.AddRange(new[] { u11, u21, u31 });

                OrgUnit u111 = new OrgUnit { Name = "老九门-吴家-吴一穷", Parent = u11 };
                OrgUnit u112 = new OrgUnit { Name = "老九门-吴家-吴二白", Parent = u11 };
                OrgUnit u113 = new OrgUnit { Name = "老九门-吴家-吴三省", Parent = u11 };
                ctx.OrgUnits.AddRange(new[] { u111, u112, u113 });

                OrgUnit u1131 = new OrgUnit { Name = "无邪", Parent=u111 };
                ctx.OrgUnits.Add(u1131);

                ctx.SaveChanges();
                Console.WriteLine($"{u.Id}, {u.Name}");
            }
        }

        static void AddLeave()
        {
            using (var ctx = new AppDbContext())
            {
                User u = new User { Name = "张三" };
                Leave l = new Leave { Requester = u, Remarks = "临时有事", Approver = u };
                ctx.Leaves.Add(l);
                ctx.SaveChanges();
                Console.WriteLine($"{l.Id}, {l.Remarks}");
            }
        }

        static void TestAddCommentForeignKey()
        {
            using (var ctx = new AppDbContext())
            {
                var c = ctx.Comments.Single(c => c.Id == 2);
                Console.WriteLine($"{c.Id}, {c.Message}, {c.ArticleId}, [c.Article is null]{c.Article is null} - {c.Article}");
            }
        }

        static void TestAddArticleSelect()
        {
            using (var ctx = new AppDbContext())
            {
                //var a = ctx.Articles
                //    .Select(a => new { a.Id, }) // 优化Select部分，限定字段
                //    .Single(c => c.Id == 2)
                //    ;
                //Console.WriteLine(c); // c是匿名类，仅包含Comment部分字段
            }
        }

        static void TestAddCommentSelect()
        {
            using (var ctx = new AppDbContext())
            {
                var c = ctx.Comments
                    .Select(c => new {c.Id, c.Message}) // 优化Select部分，限定字段
                    .Single(c => c.Id == 2)
                    ;
                Console.WriteLine(c); // c是匿名类，仅包含Comment部分字段

                var cc = ctx.Comments
                    .Include(c => c.Article)  // 加载关联数据
                    .Select(c => new { c.Article.Id }) // 优化Select部分，限定字段
                    .First()
                    ;
                Console.WriteLine(cc); // cc是匿名类

                var ccc = ctx.Comments
                    .Select(c => new { c.Article.Id }) // 优化Select部分，限定字段
                    .First()
                    ;
                Console.WriteLine(ccc); // ccc是匿名类
            }
        }

        static void TestAddComment()
        {
            try
            {
                using (var ctx = new AppDbContext())
                {
                    var c = ctx.Comments.Single(c => c.Id == 2);
                    Console.WriteLine(c);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"未加载关联Article，抛出异常：{ex}");
            }

            Console.WriteLine("---------------");

            using (var ctx = new AppDbContext())
            {
                var cc = ctx.Comments
                    .Include(c => c.Article)
                    .Single(c => c.Id == 2);
                Console.WriteLine(cc);
            }
        }

        static void TestAddArticle()
        {
            using (var ctx = new AppDbContext())
            {
                var a = ctx.Articles.First(); // 未加载Comments
                Console.WriteLine(a);

                Console.WriteLine("---------------");

                var aa = ctx.Articles
                    .Include(a => a.Comments) // 加载关联数据
                    .First();
                Console.WriteLine(aa);
            }
        }

        static void AddArticle()
        {
            using (var ctx = new AppDbContext())
            {
                Article a = new Article()
                {
                    Title = "杨中科被评为亚洲最酷程序员",
                    Content = "据报道..."
                };
                ctx.Articles.Add(a);

                Comment c1 = new Comment { Message = "太牛了" };
                a.Comments.Add(c1);

                Comment c2 = new Comment { Message = "吹牛" };
                a.Comments.Add(c2);

                ctx.SaveChanges();
            }
        }
    }
}
